package cn.easy.kitchen.data;

import cn.easy.base.utils.NameConverter;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;

/**
 * Created by caosk on 15/10/8.
 */
public class JdbcUtils {
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private Connection connection;

    public static void testConnection(DatabaseMeta databaseMeta) throws Exception {
        Class.forName(databaseMeta.getDriverClassName());
        Connection connection = DriverManager.getConnection(databaseMeta.getUrl(), databaseMeta.getUsername(), databaseMeta.getPassword());
        if(connection != null) {
            connection.close();
        }
    }

    public JdbcUtils(DatabaseMeta databaseMeta) throws ClassNotFoundException, SQLException {
        Class.forName(databaseMeta.getDriverClassName());
        connection = DriverManager.getConnection(databaseMeta.getUrl(), databaseMeta.getUsername(), databaseMeta.getPassword());
        logger.info("database connect success!");
    }

    /**
     * 释放数据库连接
     */
    public void releaseConn() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void closePstmAndResult(PreparedStatement pstmt, ResultSet resultSet) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean insertEntity(String tableName, Class c, Object obj) {
        boolean flag = false;
        if (obj == null || c.getSimpleName().equals(obj.getClass().getName()))
            return flag;
        Field[] fields = {};
        for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass
                .getSuperclass()) {
            try {
                fields = ArrayUtils.addAll(fields, superClass.getDeclaredFields());
            } catch (Exception e) {
                // Field不在当前类定义,继续向上转型
            }
        }
        int fieldSize = fields.length;
        String[] types = {"int", "boolean", "char", "float", "double", "long", "short", "byte","java.lang.Integer",
                "java.lang.String", "java.lang.Boolean","java.lang.Character", "java.lang.Float", "java.lang.Double",
                "java.lang.Long", "java.lang.Short", "java.lang.Byte", "java.math.BigDecimal", "java.util.Date"};
        List typeList = Arrays.asList(types);

        StringBuffer columnsStr = new StringBuffer();
        columnsStr.append("(");
        for(int i = 0; i < fields.length; i++){
            columnsStr.append(NameConverter.underscoreName(fields[i].getName()));
            columnsStr.append(",");
        }
        columnsStr.deleteCharAt(columnsStr.length() - 1);
        columnsStr.append(")");

        StringBuffer sql = new StringBuffer("insert into " + tableName + columnsStr.toString()
                + " values(");
        for (int i = 0; i < fieldSize; i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        logger.info("jdbc insertEntity sql: " + sql.toString());
        PreparedStatement pstmt = null;
        try {
            connection.setAutoCommit(false);
            pstmt = connection.prepareStatement(sql.toString());
            for (int j = 0; j < fieldSize; j++) {
                fields[j].setAccessible(true);
                if (typeList.contains(fields[j].getType().getName())) {
                    if (fields[j].get(obj) != null && !"".equals(fields[j].get(obj))
                            && !"null".equals(fields[j].get(obj))) {
                        pstmt.setObject(j + 1, fields[j].get(obj));
                    } else {
                        pstmt.setObject(j + 1, null);
                    }
                }
            }
            logger.warn("PreparedStatement:" + pstmt.toString());
            int result = pstmt.executeUpdate();
            connection.commit();
            flag = result > 0 ? true : false;
        } catch (Exception e1) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                logger.error("connection rollback error", e.getMessage());
            }
            e1.printStackTrace();
        } finally {
            closePstmAndResult(pstmt, null);
        }
        return flag;
    }

    /**
     * 增加、删除、改
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public boolean updateByPreparedStatement(String sql, List<Object> params) {
        boolean flag = false;
        PreparedStatement pstmt = null;
        try {
            connection.setAutoCommit(false);
            int result = -1;
            pstmt = connection.prepareStatement(sql);
            int index = 1;
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            result = pstmt.executeUpdate();
            flag = result > 0 ? true : false;
            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                logger.error("connection rollback error", e.getMessage());
            }
            e.printStackTrace();
        } finally {
            closePstmAndResult(pstmt, null);
        }
        return flag;
    }

    /**
     * 修改
     */
    public boolean update(Object entity, String tableName) throws Exception{
        boolean flag = false;
        PreparedStatement pstmt = null;
        try {
            connection.setAutoCommit(false);
            String sql = "update " + tableName + " set ";

            //获得该类所有get方法对象集合
            List<Method> list = this.matchPojoMethods(entity,"get");

            //临时Method对象,负责迭代时装method对象.
            Method tempMethod = null;

            //由于修改时不需要修改ID,所以按顺序加参数则应该把Id移到最后.
            Method idMethod = null;
            Iterator<Method> iter = list.iterator();
            while(iter.hasNext()) {
                tempMethod = iter.next();
                //如果方法名中带有ID字符串并且长度为2,则视为ID.
                if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) {
                    //把ID字段的对象存放到一个变量中,然后在集合中删掉.
                    idMethod = tempMethod;
                    iter.remove();
                    //如果方法名去掉set/get字符串以后与pojo + "id"想符合(大小写不敏感),则视为ID
                } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) {
                    idMethod = tempMethod;
                    iter.remove();
                }
            }

            //把迭代指针移到第一位
            iter = list.iterator();
            while(iter.hasNext()) {
                tempMethod = iter.next();
                sql += NameConverter.underscoreName(tempMethod.getName().substring(3)) + "= ?,";
            }

            //去掉最后一个,符号
            sql = sql.substring(0,sql.lastIndexOf(","));

            //添加条件
            sql += " where " + NameConverter.underscoreName(idMethod.getName().substring(3)) + " = ?";

            pstmt = this.connection.prepareStatement(sql);

            int i = 0;
            iter = list.iterator();
            while(iter.hasNext()) {
                Method method = iter.next();
                //此初判断返回值的类型,因为存入数据库时有的字段值格式需要改变,比如String,SQL语句是'"+abc+"'
                pstmt.setObject(++i, method.invoke(entity, new Object[]{}));
            }

            //为Id字段添加值
            pstmt.setObject(++i, idMethod.invoke(entity, new Object[]{}));

            logger.warn("PreparedStatement:" + pstmt.toString());

            //执行SQL语句
            int result = pstmt.executeUpdate();
            connection.commit();
            flag = result > 0 ? true : false;
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                logger.error("connection rollback error", e.getMessage());
            }
            e.printStackTrace();
        } finally {
            closePstmAndResult(pstmt, null);
        }
        return flag;
    }

    /**
     * 过滤当前Pojo类所有带传入字符串的Method对象,返回List集合.
     */
    private List<Method> matchPojoMethods(Object entity,String methodName) {
        //获得当前Pojo所有方法对象
        Method[] methods = entity.getClass().getDeclaredMethods();

        //List容器存放所有带get字符串的Method对象
        List<Method> list = new ArrayList<Method>();

        //过滤当前Pojo类所有带get字符串的Method对象,存入List容器
        for(int index = 0; index < methods.length; index++) {
            if(methods[index].getName().indexOf(methodName) != -1) {
                list.add(methods[index]);
            }
        }
        return list;
    }


    /**
     * 查询单条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public Map<String, Object> findOneResult(String sql, List<Object> params) {
        Map<String, Object> map = new HashMap<>();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            int index = 1;
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();//返回查询结果
            ResultSetMetaData metaData = resultSet.getMetaData();
            int col_len = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 0; i < col_len; i++) {
                    String cols_name = metaData.getColumnName(i + 1);
                    Object cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name, cols_value);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closePstmAndResult(pstmt, resultSet);
        }
        return map;
    }

    /**
     * 查询多条记录
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> findMoreResult(String sql, List<Object> params) {
        List<Map<String, Object>> list = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            int index = 1;
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnName(i + 1);
                    Object cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name, cols_value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closePstmAndResult(pstmt, resultSet);
        }
        return list;
    }

    /**
     * 通过反射机制查询单条记录
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> T findOneRefResult(String sql, List<Object> params, Class<T> cls) throws SQLException, IllegalAccessException, InstantiationException {
        T resultObject = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            int index = 1;
            logger.info("findOndRefResult sql: "+sql);
            for (int i=0; i < params.size(); i++){
                logger.info("findOndRefResult params["+ i +"]: "+(params.get(i)==null ? "" : params.get(i).toString()));
            }
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (resultSet.next()) {
                //通过反射机制创建一个实例
                resultObject = cls.newInstance();
                buildResult(resultObject, resultSet, metaData, cols_len);
            }
        } finally {
            closePstmAndResult(pstmt, resultSet);
        }
        return resultObject;

    }

    private <T> T buildResult(T resultObject, ResultSet resultSet, ResultSetMetaData metaData, int cols_len) throws SQLException {
        for (int i = 0; i < cols_len; i++) {
            String cols_name = metaData.getColumnName(i + 1);
            Object cols_value = resultSet.getObject(cols_name);
            for (Class<?> superClass = resultObject.getClass(); superClass != Object.class; superClass = superClass
                    .getSuperclass()) {
                try {
                    String clsColName = NameConverter.camelName(cols_name);
                    Field field = superClass.getDeclaredField(clsColName);
                    field.setAccessible(true); //打开javabean的访问权限
                    field.set(resultObject, cols_value);
                    break;
                } catch (Exception e) {// NOSONAR
                    logger.warn("jdbc buildResult error:" + e.getMessage());
                    // Field不在当前类定义,继续向上转型
                }
            }
        }
        return resultObject;
    }

    /**
     * 通过反射机制查询多条记录
     *
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws Exception
     */
    public <T> List<T> findMoreRefResult(String sql, List<Object> params, Class<T> cls) {
        List<T> list = new ArrayList<T>();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            int index = 1;
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (resultSet.next()) {
                //通过反射机制创建一个实例
                T resultObject = cls.newInstance();
                buildResult((T) resultObject, resultSet, metaData, cols_len);
                list.add(resultObject);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            closePstmAndResult(pstmt, resultSet);
        }
        return list;
    }

}
